package com.snsoft.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import com.snsoft.bean.Base;
import com.snsoft.bean.BaseInfo;
import com.snsoft.bean.GreenHouse;
import com.snsoft.bean.GreenHouseInfo;
import com.snsoft.bean.Sensor;
import com.snsoft.bean.SensorInfo;
import com.snsoft.util.JdbcUtils;

/**
 * 
 * @copyright ：神农大学生软件创新中心 版权所有 © 2017
 * 
 * @author 16计算机弓耀
 * 
 * @version 1.0
 * 
 * @date 2017年11月5日下午9:05:53
 * 
 * @Description TODO 环境监测模块dao
 */
public class EnvironmentDao {
	// 获取用户的所拥有的基地id列表
	public List<Map<String, Object>> getCompanyIDList(String userID) throws SQLException {
		QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select companyID from usergreenhouse where userID = ? group by companyID;";
		List<Map<String, Object>> result = runner.query(sql, new MapListHandler(), userID);
		return result;
	}

	// 获取基地
	public BaseInfo getBase(String companyID) throws SQLException {
		QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select companyID,name from company where companyID = ?;";
		BaseInfo result = runner.query(sql, new BeanHandler<BaseInfo>(BaseInfo.class), companyID);
		return result;
	}

	// 超级管理员获取基地列表
	public List<BaseInfo> getBaseListBySuper() throws SQLException {
		QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select companyID,name from company;";
		List<BaseInfo> result = runner.query(sql, new BeanListHandler<BaseInfo>(BaseInfo.class));
		return result;
	}

	// 获取基地信息
	public Base getBaseInfo(int companyID) throws SQLException {
		QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select * from company where companyID = ?;";
		Base base = runner.query(sql, new BeanHandler<Base>(Base.class), companyID);
		return base;
	}

	// 获取温室列表
	public List<GreenHouseInfo> getGreenHouseList(int companyID) throws SQLException {
		QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select greenHouseID,number from greenhouseview where companyID = ?;";
		List<GreenHouseInfo> green = runner.query(sql, new BeanListHandler<GreenHouseInfo>(GreenHouseInfo.class),
				companyID);
		return green;

	}

	// 获取温室信息
	public GreenHouse getGreenHouseInfo(int greenHouseID) throws SQLException {
		QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select * from greenhouse where greenHouseID = ?;";
		GreenHouse green = runner.query(sql, new BeanHandler<GreenHouse>(GreenHouse.class), greenHouseID);
		return green;
	}

	// 获取温室中传感器最新数据
	public List<Sensor> getNewData(int greenHouseID, int time) throws SQLException {
		QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select * from sensordataview where greenHouseID = ? and TO_DAYS(NOW()) - TO_DAYS(recordTime) < ? limit 0,100;";
		Object params[] = { greenHouseID, time };
		List<Sensor> sensor = runner.query(sql, new BeanListHandler<Sensor>(Sensor.class), params);
		return sensor;
	}

	// 获取单个传感器历史数据
	public List<SensorInfo> getHistoryData(int protocolID, int deviceID, int nodeID, int time) throws SQLException {
		QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select * from sensordataview where protocolID = ? and deviceID = ? and nodeID = ? and TO_DAYS(NOW()) - TO_DAYS(recordTime) < ? limit 0,100;";
		Object params[] = { protocolID, deviceID, nodeID, time };
		List<SensorInfo> sensorinfo = runner.query(sql, new BeanListHandler<SensorInfo>(SensorInfo.class), params);
		return sensorinfo;
	}

}
